package com.atguigu.realtime.util;

import com.alibaba.fastjson.JSONObject;
import com.atguigu.realtime.common.Constant;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.flink.shaded.guava18.com.google.common.base.CaseFormat;

import java.lang.reflect.InvocationTargetException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author lzc
 * @Date 2022/7/16 14:06
 */
public class JdbcUtil {
    
    public static Connection getPhoenixConnection() {
        String driver = Constant.PHOENIX_DRIVER;
        String url = Constant.PHOENIX_URL;
        
        try {
            return getJdbcConnection(driver, url, null, null);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            throw new RuntimeException("phoenix的驱动没有找到, 请确认是否添加了phoenix依赖....");
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("url拼写错误, 或者HBase是否正常...");
        }
    }
    
    public static Connection getMySqlConnection() throws SQLException, ClassNotFoundException {
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://hadoop162:3306/gmall_config?useSSL=false";
        return getJdbcConnection(driver, url, "root", "aaaaaa");
    }
    
    
    public static Connection getJdbcConnection(String driver,
                                               String url,
                                               String user,
                                               String password) throws ClassNotFoundException, SQLException {
        // 加载驱动
        Class.forName(driver);
        
        // 获取连接
        return DriverManager.getConnection(url, user, password);
    }
    
    public static void closeConnection(Connection conn) {
        try {
            if (conn != null && !conn.isClosed()) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    // 对所有数据库一视同仁
    // 是否下划线转驼峰
    public static <T> List<T> queryList(Connection conn,
                                        String querySql,
                                        Object[] args,
                                        Class<T> tClass,
                                        boolean ... underlineToCaseCamel) throws SQLException, IllegalAccessException, InstantiationException, InvocationTargetException {
        
        // 默认不转, 如果不传就不转
        boolean isToCamel = false;
        if (underlineToCaseCamel.length == 1) {
            isToCamel = underlineToCaseCamel[0];
        }
        
        
        ArrayList<T> result = new ArrayList<>();
        
        // 1. 根据查询语句得到一个预处理语句
        PreparedStatement ps = conn.prepareStatement(querySql);
        // 2. 给占位符赋值
        for (int i = 0; args != null && i < args.length; i++) {
            ps.setObject(i + 1, args[i]);
        }
        // 3. 执行查询语句
        ResultSet resultSet = ps.executeQuery();
        ResultSetMetaData metaData = resultSet.getMetaData();  // 获取结果集中的元数据
        int columnCount = metaData.getColumnCount();   // 每一行列的数量
        // 4. 返回执行的结果: ResultSet, 解析结果集, 把每行封装到一个T类型的对象中, 放入到result集合中
        while (resultSet.next()) {  // 读取每一行
            // 每行封装一个T类型的对象, 每一列就是对象中的一个属性
            T t = tClass.newInstance(); // 调用T这个类无参构造器创建对象
            // 把这行数据的每一列全部读出来, 遍历每一列
            for (int i = 1; i <= columnCount; i++) {  // 如果忘记加=, 将来读的数据中会少一列
                // 读取列名
                String name = metaData.getColumnLabel(i);
                if (isToCamel) {  // 把下划线转成驼峰
                    name = CaseFormat.LOWER_UNDERSCORE.to(CaseFormat.LOWER_CAMEL, name);
                }
                
                Object value = resultSet.getObject(i);
                
                // 给T类型的对象中 name这个属性赋值为value
                BeanUtils.setProperty(t, name, value);
            }
            result.add(t);
        }
        
        
        return result;
    }
    
    public static void main(
        String[] args) throws InvocationTargetException, SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {
        List<JSONObject> list = queryList(getPhoenixConnection(), "select * from dim_base_trademark where id=?", new Object[]{"3"}, JSONObject.class);
        for (JSONObject object : list) {
            System.out.println(object);
        }
        
        //        List<JSONObject> list = queryList(getMySqlConnection(), "select * from sku_info", null, JSONObject.class);
        
        /*List<TableProcess> list = queryList(getMySqlConnection(), "select * from table_process", null, TableProcess.class, true);
        
        
        for (TableProcess object : list) {
            System.out.println(object);
        }*/
    }
}
